package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.sq.partner.manager.common.exception.AppException;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.INlifeProdDao;
import com.sq.partner.manager.mybatis.model.NlifeCoreProd;
import com.sq.partner.manager.mybatis.model.NlifeProd;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;

@Repository
public class NlifeProdDaoImpl extends BaseDaoImpl implements INlifeProdDao {
	
	private static Logger logger = LogUtil.getLog(NlifeProdDaoImpl.class);
	
	@Override
	public Page<NlifeProd> queryProdPageByCriteria(Page<NlifeProd> page) throws Exception {
		
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.PROD_CODE AS PRODCODE,T.PROD_NAME AS PRODNAME,T.STATUS,"
					+ "TO_CHAR(T.START_DATE, 'yyyy-MM-dd HH24:MI:SS') AS STARTDATE,T.INSTANT, T.POOL_TYPE AS POOLTYPE  FROM T_NLIFE_PROD T WHERE 1 = 1");
			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			NlifeProd NlifeProd= page.getType();
			if(StrUtil.isNotEmpty(NlifeProd.getProdCode())){
				sqlbuf.append(" AND T.PROD_CODE like ?");
				list.add(NlifeProd.getProdCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(NlifeProd.getProdName())){
				sqlbuf.append(" AND T.PROD_NAME like ?");
				list.add(NlifeProd.getProdName());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(NlifeProd.getStatus())){
				sqlbuf.append(" AND T.STATUS = ? ");
				list.add(NlifeProd.getStatus());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(NlifeProd.getVstartDate())){
				sqlbuf.append(" AND T.START_DATE >= to_date(? ,'yyyy-mm-dd')");
				list.add(NlifeProd.getVstartDate());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(NlifeProd.getVendDate())){
				sqlbuf.append(" AND T.START_DATE  < to_date(? ,'yyyy-mm-dd') +1");
				list.add(NlifeProd.getVendDate());
				islike.add(false);
			}
			sqlbuf.append(" ORDER BY NVL(T.OPER_TIME, NVL(T.START_DATE, TO_DATE('1990/01/01', 'yyyy/mm/dd'))) DESC, T.PROD_CODE DESC");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public boolean saveProd(NlifeProd prod) throws Exception {
		return this.add("insertNlifeProd", prod);
	}

	@Override
	public boolean updateProd(NlifeProd prod) throws Exception {
		return this.update("updateNlifeProdNotNull", prod);
	}

	@Override
	public boolean deleteProd(String prodCode) throws Exception {
		return this.delete("delNlifeProdById",prodCode);
	}

	@Override
	public Page<NlifeCoreProd> queryNLifeCoreProd(Page<NlifeCoreProd> page) throws Exception {
		
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT T.CORE_PROD_CODE AS COREPRODCODE, T.CORE_PROD_NAME AS COREPRODNAME, T.RISK_MAIN_TYPE AS RISKMAINTYPE"
					+ "  FROM T_NLIFE_CORE_PROD T WHERE 1 = 1 ");
 			String sql;
			List<String> list=new ArrayList<String>();
			List<Boolean> islike=new ArrayList<Boolean>();
			NlifeCoreProd nlifeCoreProd= page.getType();
			if(StrUtil.isNotEmpty(nlifeCoreProd.getCoreProdCode())){
				sqlbuf.append("AND T.CORE_PROD_CODE LIKE ? ");
				list.add(nlifeCoreProd.getCoreProdCode());
				islike.add(true);
			}
			if(StrUtil.isNotEmpty(nlifeCoreProd.getCoreProdName())){
				sqlbuf.append("AND T.CORE_PROD_NAME LIKE ? ");
				list.add(nlifeCoreProd.getCoreProdName());
				islike.add(true);
			}
			sqlbuf.append(" ORDER BY T.CORE_PROD_CODE ASC");
			sql=sqlbuf.toString();
			logger.debug("执行SQL：" + sql);
			pstmt = conn.prepareStatement("select count(*) from ("+sql+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total = rs.getLong(1);
			
			//查出分页
			page.setTotal(total);
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sql,page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	}

	@Override
	public String findMaxNewLifeTradProdCode(String coreProdCode) throws Exception {
		return (String) this.query("findCurrMaxTradProdCodeByCoreProdCode", coreProdCode);
	}

	@Override
	public NlifeProd queryProdInfoById(String param) throws Exception {
		return (NlifeProd) this.query("queryNlifeProdById", param);
	}

	@Override
	public List<NlifeProd> queryAllCoreProdInfoList() throws Exception {
		int a=0;
		return   this.queryList("queryAllCoreProdInfoFromDB", a);
	}
	
	@Override
	public Page<NlifeProd> queryPageByCriteria(Page<NlifeProd> page)
			throws Exception {
		Map<String,Object> paramMap = page.getMap();
		
		int total = (Integer) this.query("queryNlifeProdCountByFilter", paramMap);
		page.setTotal(total);
		
		List<NlifeProd> resultList = this.queryList("queryNlifeProdPageByFilter1", paramMap);
		page.setData(resultList);

		return page;
	}

	@Override
	public boolean updateSaleCode(Map<String, String> map) throws Exception {
		// TODO Auto-generated method stub
		return this.update("updateNlifeProdSaleCode", map);
	}

	@Override
	public boolean updateRiskMain(Map<String, String> map) throws Exception {
		// TODO Auto-generated method stub
		return  this.update("updateNlifeProdRiskMain", map);
	}

	@Override
	public List<Map<String, Object>> impNlifeProd(Map<String, Object>map) throws Exception {
		// TODO Auto-generated method stub
		return  this.queryList("impNlifeProdByMap", map);
	}

	@Override
	public List<Map<String, Object>> impNlifeProdRation(Map<String, Object> map)
			throws Exception {
		// TODO Auto-generated method stub
		return  this.queryList("impNlifeProdRationByMap", map);
	}
	
	/**
	 * 根据方案号planNo获取新意健险产品特殊标识
	 * */	
	@Override
	public String getSpecTypeInNlifeProd(String planNo) throws Exception {
		
		return (String)this.query("getSpecTypeInNlifeProd", planNo);
	}
	
	/**
	 * 执行多条Insert语句
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	@Override
	public boolean insertProdInfoWithRiskCal(String sql) throws Exception {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		boolean flag = false;
		try {
			connection = this.getConnection();
			connection.setAutoCommit(false);
			String[] splitSql = sql.split(";");
			for (String s : splitSql) {
				if (!s.contains("insert") && !s.contains("INSERT")) continue;
				String executeSql = s.replace(";", " ");
				preparedStatement = connection.prepareStatement(executeSql);
				flag = preparedStatement.execute();
				close(preparedStatement);
			}
			connection.commit();
			return flag;
		} catch (Exception e) {
			if (connection != null) connection.rollback();
			throw new AppException("复制新增执行SQL出错:" + e.getMessage());
		} finally {
			close(connection);
		}
	}

	@Override
	public void updateUpdateProdSpecialArran(List<Map<String, String>> param) throws Exception {
		if(param != null && param.size() > 0){
			for(Map<String,String> map : param){
				this.update("updateUpdateProdSpecialArran", map);
			}
		}
		
	}
}
